﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_WMG006:DA_Matrix
    {

        public DA_WMG006()
        {
            tableName = "WMG.dbo.F_B_MF_MONTHLY_CUST_AUM";
        }
       
        public DataTable GetSummaryData(string p_t, string[] p_months
            , string[] p_vals)
        {
            string sql;
            string[] criteria = null;
            sql = string.Empty;
            StringBuilder sb = new StringBuilder();
            //當 p_vals 為 null 表示是第一次取資料
            if (p_vals != null)
            {
                switch (p_t)
                {
                    case "CO":
                        sql = getCoSQLStatement(p_vals.Count());
                        criteria = new string[] { " AND A.MUTUAL_FUND_COMPANY_CODE='{0}'"
                                    , " AND A.MUTUAL_FUND_CODE ='{0}'" };
                        break;
                    case "Cust":
                        sql = getCustSQLStatement(p_vals.Count());                        
                        criteria = new string[] { " AND B.AO_LEVEL='{0}'"
                                    , " AND A.MUTUAL_FUND_COMPANY_CODE='{0}'" };
                        break;
                }
                for (int i = 0; i < p_vals.Length; i++)
                {
                    sb.AppendFormat(criteria[i], p_vals[i]);
                }
            }
            else
            {
                sql = p_t.Equals("CO") ?getSQLStatementByCompany()
                    :getSQLStatementByAOLevel();
            }

            FillTableSchema(tableName);
            GetCriteriaString(DBOpt.Oracle);
            /*
            sql = string.Format(sql, CriteriaString + sb.ToString()
                , getFieldString(p_months,@"
                 ,MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.庫存成本 END) [{0}庫存成本]
	             ,MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.庫存現值 END) [{0}庫存現值]"
                ));
             */
            sql = string.Format(sql, CriteriaString + sb.ToString()
                           , getFieldString(p_months, @"
                 ,MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.庫存成本 END) 庫存成本{0}
	             ,MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.庫存現值 END) 庫存現值{0}"
                           ));

            AddParameter(sql,DBOpt.Oracle);
            return getData();
        }

        public DataTable GetReturnata(string p_month)
        {
            string sql = string.Format(getReturnStatement(), p_month);
            return getData(sql, "Return",DBOpt.Oracle);
        }

        public DataTable GetReturnata(string[] p_months)
        {
            FillTableSchema(tableName);
            GetCriteriaString();
            string fld = @"
 ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.基金名稱 END) AS 基金名稱{0}
 ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.庫存現值 END) AS 庫存現值{0}
 ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.庫存成本 END) AS 庫存成本{0}
 ,MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.報酬率 END) AS 報酬率{0}
";
            string sql = string.Format(getReturnStatement() 
                , getFieldString(p_months,fld)
                , CriteriaString.Replace("AND YYYYMM", "AND A.YYYYMM")
                                .Replace('@',':'));
            AddParameter(sql,DBOpt.Oracle);
            //return getData(sql, "Return", DBOpt.Oracle);
            return getData(cmd);
        }

        /// <summary>
        /// 庫存基金排名前十大
        /// </summary>
        /// <param name="p_months">傳入12個月份陣列</param>
        /// <returns></returns>
        public DataTable GetStockTopTen(string[] p_months,string p_month)
        {
            string fld0, fld1,v1,sql;
            StringBuilder sb = new StringBuilder();
            foreach (string mon in p_months)
            {
                if (!mon.Equals(p_month))
                {
                    sb.AppendFormat(",'{0}'", mon);
                }
                else
                {
                    sb.AppendFormat(",'{0}'", mon);
                    break;
                }
            }
            v1 = sb.ToString().Substring(1);
            fld0 = Utility.GetYearMonthString(p_months
                ,@"
    ,A.""{0}銷量""
    ,(A.""{0}庫存成本"" + A.""{0}銷量"" - A.""{0}庫存成本"") AS ""{0}贖回""
    ,A.""{0}庫存成本""
    ,A.""{0}報酬率""");
            fld1 = Utility.GetYearMonthString(p_months
                , @"
    ,MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.銷量 END) ""{0}銷量""
	,MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.庫存成本 END) ""{0}庫存成本""
	,MAX(CASE WHEN A.YYYYMM = '{0}' THEN A.報酬率 END) ""{0}報酬率""");
            sql = string.Format(getStockTopTenStatement()
                ,fld0,fld1,p_month,v1);
            return getData(sql, "stock", DBOpt.Oracle);
        }

        protected string getCoSQLStatement(int p)
        {
            string[] coSQL = {"", getSQLStatementByFund()
                             ,getSQLStatementByAOLevel()};
                             
            return coSQL[p];
        }

        protected string getCustSQLStatement(int p)
        {
            string[] custSQL = {"" ,getSQLStatementByCompany()
                                   ,getSQLStatementByFund() };
            return custSQL[p];
        }

        protected string getSQLStatementByFund()
        {
            /*
             * AND A.YYYYMM = '201101' AND B.AO_LEVEL = '1'
			AND A.MUTUAL_FUND_COMPANY_CODE = '59'
             * ,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.庫存成本 END) [201101庫存成本]
                ,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.庫存現值	END) [201101庫存現值]
             */
            /* MS SQL Statement
            return @"
SELECT
	ISNULL(A.MUTUAL_FUND_FULL_ABBR,'合計') [基金名稱]
	{1}	
FROM (	

	SELECT		
		A.MUTUAL_FUND_CODE + '_' +B.MUTUAL_FUND_FULL_ABBR [MUTUAL_FUND_FULL_ABBR]
		,A.YYYYMM
		,CONVERT(NVARCHAR,CAST(SUM(A.庫存成本)	 AS MONEY),1) 庫存成本
		,CONVERT(NVARCHAR,CAST(SUM(A.庫存現值)	 AS MONEY),1) 庫存現值
		,GROUPING_ID(A.YYYYMM , B.MUTUAL_FUND_FULL_ABBR , A.MUTUAL_FUND_CODE) 	GRP_ID
	FROM(		
			SELECT
				A.YYYYMM
				,A.MUTUAL_FUND_COMPANY_CODE
				,A.MUTUAL_FUND_CODE
				,A.庫存現值
				,A.庫存成本
			  FROM WMG.dbo.F_B_MF_MONTHLY_CUST_AUM A
			  LEFT JOIN WMG.dbo.D_WM_AO_CUST_M B  
				ON A.YYYYMM = B.YEARMONTH
				AND A.BRANCH_NBR = B.BRANCH_NBR
			   AND A.CUSTOMER_ID = B.PERSON_ID
			WHERE 1=1
			 {0}
	)A LEFT OUTER JOIN D_B_MF_INFO B
	ON A.YYYYMM = B.YYYYMM AND A.MUTUAL_FUND_CODE = B.MUTUAL_FUND_CODE 
        AND B.MUTUAL_FUND_COMPANY_CODE = A.MUTUAL_FUND_COMPANY_CODE	
	GROUP BY ROLLUP(A.YYYYMM,B.MUTUAL_FUND_FULL_ABBR,A.MUTUAL_FUND_CODE)
)A	
WHERE A.GRP_ID IN(0,3)
GROUP BY A.MUTUAL_FUND_FULL_ABBR
ORDER BY CASE WHEN A.MUTUAL_FUND_FULL_ABBR IS NULL THEN '999999' ELSE A.MUTUAL_FUND_FULL_ABBR END";
             */
            return @"

SELECT
	CASE WHEN A.MUTUAL_FUND_FULL_ABBR = '_' THEN  '合計' ELSE A.MUTUAL_FUND_FULL_ABBR  END  基金名稱	
	{1}
FROM (	

	SELECT		
		A.MUTUAL_FUND_CODE || '_' || B.MUTUAL_FUND_FULL_ABBR MUTUAL_FUND_FULL_ABBR
		,A.YYYYMM
		,TO_CHAR(SUM(A.庫存成本), 'FM999,999,999,999,999') 庫存成本
		,TO_CHAR(SUM(A.庫存現值), 'FM999,999,999,999,999') 庫存現值		
		,GROUPING_ID(A.YYYYMM , B.MUTUAL_FUND_FULL_ABBR , A.MUTUAL_FUND_CODE) 	GRP_ID
	FROM(		
			SELECT
				A.YYYYMM
				,A.MUTUAL_FUND_COMPANY_CODE
				,A.MUTUAL_FUND_CODE
				,A.庫存現值
				,A.庫存成本
			  FROM X_Fund_CUST_AUM_2 A
			  LEFT JOIN X_Fund_AO_CUST_M B  
				ON A.YYYYMM = B.YEARMONTH
				AND A.BRANCH_NBR = B.BRANCH_NBR
			   AND A.CUSTOMER_ID = B.PERSON_ID
			WHERE 1=1
			  {0}
	)A LEFT OUTER JOIN DM_F_VIEW.D_B_MF_INFO B
	ON A.YYYYMM = B.YYYYMM AND A.MUTUAL_FUND_CODE = B.MUTUAL_FUND_CODE 
        AND B.MUTUAL_FUND_COMPANY_CODE = A.MUTUAL_FUND_COMPANY_CODE	
	GROUP BY ROLLUP(A.YYYYMM,B.MUTUAL_FUND_FULL_ABBR,A.MUTUAL_FUND_CODE)
)A	
WHERE A.GRP_ID IN(0,3)
GROUP BY A.MUTUAL_FUND_FULL_ABBR
ORDER BY CASE WHEN A.MUTUAL_FUND_FULL_ABBR IS NULL THEN '999999' ELSE A.MUTUAL_FUND_FULL_ABBR END
";
        }

        protected string getSQLStatementByCompany()
        {
            /*
             * {0} AND A.YYYYMM IN('201101','201102')
             * {1} ,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.庫存成本 END) [201101庫存成本]
             *     ,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.庫存現值	END) [201101庫存現值]
             */
            /*
            return @"
SELECT
	ISNULL(B.mutual_fund_company_code + '_' + B.mutual_fund_company_name,'合計') AS [基金公司]
    {1}
FROM(
SELECT
	A.YYYYMM
	,A.MUTUAL_FUND_COMPANY_CODE
	,CONVERT(NVARCHAR,CAST(SUM(A.庫存成本)	 AS MONEY),1) 庫存成本
	,CONVERT(NVARCHAR,CAST(SUM(A.庫存現值)	 AS MONEY),1) 庫存現值
  FROM WMG.dbo.F_B_MF_MONTHLY_CUST_AUM A
  LEFT JOIN WMG.dbo.D_WM_AO_CUST_M B  
    ON A.YYYYMM = B.YEARMONTH
    AND A.BRANCH_NBR = B.BRANCH_NBR
   AND A.CUSTOMER_ID = B.PERSON_ID
WHERE 1=1
{0}
GROUP BY CUBE(A.YYYYMM,A.MUTUAL_FUND_COMPANY_CODE)
)A LEFT OUTER JOIN [D_B_MF_COMPANY] B
ON A.MUTUAL_FUND_COMPANY_CODE = B.mutual_fund_company_code
GROUP BY B.mutual_fund_company_name,B.mutual_fund_company_code
ORDER BY CASE WHEN B.mutual_fund_company_name IS NULL THEN ' ' ELSE B.mutual_fund_company_name END DESC";
             */
            return @"

SELECT
	CASE WHEN B.mutual_fund_company_code IS NULL THEN '合計'
		ELSE B.mutual_fund_company_code || '_' || B.mutual_fund_company_name
		END 基金公司      	
    {1}
FROM
(
	SELECT
		A.YYYYMM
		,A.MUTUAL_FUND_COMPANY_CODE
		,TO_CHAR(SUM(A.庫存成本), 'FM999,999,999,999,999') 庫存成本
		,TO_CHAR(SUM(A.庫存現值), 'FM999,999,999,999,999') 庫存現值		
	 FROM X_Fund_CUST_AUM_2 A
	  LEFT JOIN X_Fund_AO_CUST_M B  
		ON A.YYYYMM = B.YEARMONTH
		AND A.BRANCH_NBR = B.BRANCH_NBR
	   AND A.CUSTOMER_ID = B.PERSON_ID
	WHERE 1=1
		{0}
	GROUP BY CUBE(A.YYYYMM,A.MUTUAL_FUND_COMPANY_CODE)
)A LEFT OUTER JOIN DM_F_VIEW.D_B_MF_COMPANY B
ON A.MUTUAL_FUND_COMPANY_CODE = B.mutual_fund_company_code
GROUP BY B.mutual_fund_company_name,B.mutual_fund_company_code
ORDER BY CASE 
	WHEN B.mutual_fund_company_name IS NULL 
		THEN ' ' 
		ELSE B.mutual_fund_company_name 
	END DESC
";

        }

        protected string getSQLStatementByAOLevel()
        {
            /*
             *{0} A.YYYYMM IN('201101','201102')
             *{1},MAX(CASE WHEN A.YYYYMM = '201101' THEN A.庫存成本 END) [201101庫存成本]
	             ,MAX(CASE WHEN A.YYYYMM = '201101' THEN A.庫存現值 END) [201101庫存現值]
             */
            /*
            return @"
SELECT
    A.AO_LEVEL
	{1}
FROM(
		SELECT 
			CASE WHEN A.YYYYMM IS NULL THEN '月份小計' ELSE A.YYYYMM END YYYYMM
			,ISNULL(A.AO_LEVEL,'合計')AO_LEVEL
			,CONVERT(NVARCHAR,CAST(SUM(A.庫存成本)	 AS MONEY),1) 庫存成本
			,CONVERT(NVARCHAR,CAST(SUM(A.庫存現值)	 AS MONEY),1) 庫存現值
		FROM (
				SELECT	    
						A.YYYYMM	
						,CONVERT(NVARCHAR(2),ISNULL(B.ao_level,'Z')) AO_LEVEL	    
						,A.庫存現值
						,A.庫存成本        
					FROM WMG.dbo.F_B_MF_MONTHLY_CUST_AUM A LEFT JOIN WMG.dbo.D_WM_AO_CUST_M B  
					ON A.YYYYMM = B.YEARMONTH 
						AND A.BRANCH_NBR = B.BRANCH_NBR
						AND A.CUSTOMER_ID = B.PERSON_ID
					WHERE 1=1 
					 {0}
			 )A
		GROUP BY CUBE( A.YYYYMM,A.ao_level )
)A     
GROUP BY A.AO_LEVEL
ORDER BY A.AO_LEVEL
";
             */
            return @"

SELECT
    A.AO_LEVEL	
	{1}
FROM(
		SELECT 
			CASE WHEN A.YYYYMM IS NULL THEN '月份小計' ELSE A.YYYYMM END YYYYMM
			,NVL(A.AO_LEVEL,'合計')AO_LEVEL
			,TO_CHAR(SUM(A.庫存成本), 'FM999,999,999,999,999') 庫存成本
			,TO_CHAR(SUM(A.庫存現值), 'FM999,999,999,999,999') 庫存現值
		FROM
		(
			SELECT	    
				A.YYYYMM	
				,nvl(B.ao_level,'Z') AO_LEVEL	    
				,A.庫存現值
				,A.庫存成本        
			FROM X_Fund_CUST_AUM_2 A LEFT JOIN X_Fund_AO_CUST_M B  
			ON A.YYYYMM = B.YEARMONTH 
				AND A.BRANCH_NBR = B.BRANCH_NBR
				AND A.CUSTOMER_ID = B.PERSON_ID
			WHERE 1=1 
			  {0}
		 )A
		GROUP BY CUBE( A.YYYYMM,A.ao_level )
)A     
GROUP BY A.AO_LEVEL
ORDER BY A.AO_LEVEL
";
        }

        public override string[] GetDistinctField()
        {
            return new string[] { "YYYYMM" };
        }

        protected string getFieldString(string[] p_months,string p_fld)
        {            
            //StringBuilder sb = new StringBuilder();
            //foreach (string mon in p_months.Reverse())
            //{
            //    sb.AppendFormat(p_fld, mon);
            //}
            //return sb.ToString();
            return Utility.GetYearMonthString(p_months, p_fld);
        }

        protected string getReturnStatement()
        {
            return @"
SELECT 
  A.SEQ
  {0}
FROM (
	SELECT
	  A.YYYYMM
	  ,A.基金名稱
	  ,A.庫存現值
	  ,A.庫存成本
	  ,TO_CHAR(A.報酬率,'FM999.00')   AS 報酬率
	  ,ROW_NUMBER() OVER(PARTITION BY A.YYYYMM ORDER BY A.庫存成本 DESC) SEQ
	FROM(		
		SELECT
		*
		FROM X_F_B_MF_MONTHLY_CUST_AUM a
		WHERE 1=1
		{1}
	)A
)A
WHERE A.SEQ < 51
GROUP BY A.SEQ
ORDER BY A.SEQ 
";
        }
       
        protected string getStockTopTenStatement()
        {
            /*{0}
    ,A."201112銷量"
    ,A."201111庫存成本" + A."201112銷量" - A."201112庫存成本" AS "201112贖回"
    ,A."201112庫存成本"
    ,A."201112報酬率"             
             * {1}
		,MAX(CASE WHEN A.YYYYMM = '201112' THEN A.銷量 END) "201112銷量"
		,MAX(CASE WHEN A.YYYYMM = '201112' THEN A.庫存成本 END) "201112庫存成本"
		,MAX(CASE WHEN A.YYYYMM = '201112' THEN A.報酬率 END) "201112報酬率"
             
             */
            return @"
SELECT
    A.MUTUAL_FUND_FULL_ABBR
    {0}  
	FROM(
	SELECT
		A.MUTUAL_FUND_FULL_ABBR
        {1}				
	FROM X_STOCK_TOP_TEN B
	LEFT OUTER JOIN 
	(
		SELECT
			B.YYYYMM
			,B.MUTUAL_FUND_FULL_ABBR
			,B.庫存成本/100000000 AS 庫存成本
			,B.庫存現值/100000000 AS 庫存現值
			,ROUND(B.報酬率,2)報酬率
			,C.銷量/100000000 AS 銷量
		FROM X_STOCK_庫存成本 B
		LEFT OUTER JOIN X_SALE_銷量 C
		ON B.YYYYMM = C.YYYYMM 
		AND B.MUTUAL_FUND_FULL_ABBR = C.MUTUAL_FUND_FULL_ABBR
        WHERE B.YYYYMM IN ({3})
	)A ON B.MUTUAL_FUND_FULL_ABBR = A.MUTUAL_FUND_FULL_ABBR
	WHERE B.SEQ <11  
	AND B.YYYYMM = '{2}'
	GROUP BY A.MUTUAL_FUND_FULL_ABBR,B.SEQ 
	ORDER BY B.SEQ 	
)A
";
        }

        public override DataSet GetCriteria()
        {
            /*改成直接取ODS上我自己建的資料
             */
            string sql = "SELECT YYYYMM FROM X_MONTH ORDER BY YYYYMM  DESC";
            DataSet ds = new DataSet();
            DataTable dt = getData(sql, "YYYYMM", DBOpt.Oracle);
            ds.Tables.Add(dt);
            return ds;
        }
    }
}
